#!/usr/bin/python
# -*- coding: UTF-8 -*-


################################################################################
#
# Copyright (c) 2020 openEuler.org, Inc. All Rights Reserved
#
################################################################################
"""
Interaction between file and mysql data

Authors: xiaojianghui
Date:    10/22/2020 11:01 AM
"""

import os
import xlwt
from dbConnecttion.MysqlConn import Mysql
import time
import shutil
from common import times


def cur_date():
    """
    current date
    :return create_time: string
    """
    create_time = time.strftime("%Y-%m-%d", time.localtime())
    return create_time


class MysqlToExcel(object):
    """Error data is exported from mysql to excel"""

    def __init__(self):
        """Error data is exported from mysql to excel"""
        self.file_name = './problemexcels/error_data_' + str(cur_date()) + ".xls"

    @staticmethod
    def get_results():
        """Query error data
        :return results: list
        """
        mysql = Mysql()
        last_month = times.last_month_date()
        sql = "select * from cve_origin_excel where (cve_status = 3 or cve_status=4) and is_export = 1 and create_time > %s"
        val = (last_month,)
        results = mysql.getMany(sql, val)
        print(results)
        mysql.dispose()
        mysql.close()
        return results

    def generate_table(self):
        """Generate excel sheet, File already exists, delete"""
        if os.listdir("./problemexcels"):
            shutil.rmtree("./problemexcels")
            os.mkdir("./problemexcels")
        results = self.get_results()
        if not results:
            print("The query result is empty, no error data")
            return
        # Create excel object
        f = xlwt.Workbook()
        sheet1 = f.add_sheet('sheet1', cell_overwrite_ok=True)
        # Column field
        column_names = ['cve_id', 'cve_num', 'cve_url', 'cve_version', 'pack_name', 'score_type', 'nvd_score',
                        'cve_level', 'cve_desc',
                        'repair_time', 'vector_value', 'attack_vector', 'access_vector', 'attack_complexity',
                        'access_complexity',
                        'privilege_required', 'user_interaction', 'scope', 'confidentiality', 'integrity',
                        'availability',
                        'authentication', 'cve_status', 'create_time', 'update_time',
                        'delete_time']
        # Write the first row, column name
        for i in range(0, len(column_names)):
            sheet1.write(0, i, column_names[i])
        # Write multiple lines
        num = 0
        for i in results:
            for key in i:
                if type(i[key]) == bytes:
                    i[key] = i[key].decode("utf-8")
            sheet1.write(num + 1, 0, i["cve_id"])
            sheet1.write(num + 1, 1, i["cve_num"])
            sheet1.write(num + 1, 2, i["cve_url"])
            sheet1.write(num + 1, 3, i["cve_version"])
            sheet1.write(num + 1, 4, i["pack_name"])
            sheet1.write(num + 1, 5, i["score_type"])
            sheet1.write(num + 1, 6, i["nvd_score"])
            sheet1.write(num + 1, 7, i["cve_level"])
            sheet1.write(num + 1, 8, i["cve_desc"])
            sheet1.write(num + 1, 9, i["repair_time"])
            sheet1.write(num + 1, 10, i["vector_value"])
            sheet1.write(num + 1, 11, i["attack_vector"])
            sheet1.write(num + 1, 12, i["access_vector"])
            sheet1.write(num + 1, 13, i["attack_complexity"])
            sheet1.write(num + 1, 14, i["access_complexity"])
            sheet1.write(num + 1, 15, i["privilege_required"])
            sheet1.write(num + 1, 16, i["user_interaction"])
            sheet1.write(num + 1, 17, i["scope"])
            sheet1.write(num + 1, 18, i["confidentiality"])
            sheet1.write(num + 1, 19, i["integrity"])
            sheet1.write(num + 1, 20, i["availability"])
            sheet1.write(num + 1, 21, i["authentication"])
            sheet1.write(num + 1, 22, i["cve_status"])
            if i['create_time']:
                sheet1.write(num + 1, 23, i["create_time"].strftime('%Y-%m-%d %H:%M:%S'))
            else:
                sheet1.write(num + 1, 23, i['create_time'])
            if i["update_time"]:
                sheet1.write(num + 1, 24, i["update_time"].strftime('%Y-%m-%d %H:%M:%S'))
            else:
                sheet1.write(num + 1, 24, i["update_time"])
            if i["delete_time"]:
                sheet1.write(num + 1, 25, i["delete_time"].strftime('%Y-%m-%d %H:%M:%S'))
            else:
                sheet1.write(num + 1, 25, i["delete_time"])
            num += 1
        # save document
        f.save(self.file_name)
        # Determine whether the file exists
        if not os.path.exists(self.file_name):
            print("Failed to generate excel")
        else:
            print("Excel generated successfully")

    @staticmethod
    def update_status():
        """
        Change the data with is_export 1 to 2 to indicate that it has been exported in excel
        """
        mysql = Mysql()
        sql = "update cve_origin_excel set is_export=2 where cve_status=3 or cve_status=4"
        mysql.update(sql)
        mysql.dispose()
        mysql.close()
